﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.IO;
using sqlserverextensions.ExportOperations;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure()]
    public static void QueryToCsv(SqlString fileName
                                , SqlString sqlStatement
                                , SqlBoolean multiQuery
                                , SqlBoolean columnHeader
                                , SqlString fieldDelimiter
                                , SqlString rowDelimiter)
    {
        TextWriter strWriter;

        ExportHelper.ValidateMandatoryParameters(fileName, sqlStatement, multiQuery, columnHeader);

        if (string.IsNullOrEmpty(fieldDelimiter.ToString()))
            fieldDelimiter = ";";

        if (string.IsNullOrEmpty(rowDelimiter.ToString()))
            rowDelimiter = "\r\n";

        if (multiQuery)
        {
            DataSet dataSet = ExportHelper.GetDataSet(sqlStatement.ToString());

            strWriter = new StreamWriter(fileName.ToString(), false);
            foreach (DataTable dt in dataSet.Tables)
            {
                ExportHelper.Export2Csv(dt, ref strWriter, fieldDelimiter.ToString(), rowDelimiter.ToString(), columnHeader.Value);
                if (dataSet.Tables[dataSet.Tables.Count - 1] != null)
                    strWriter.WriteLine();

                strWriter.Flush();
            }
            strWriter.Close();
            strWriter.Dispose();

            dataSet.Dispose();
        }
        else
        {
            using (SqlConnection connection = new SqlConnection("context connection=true"))
            {
                connection.Open();

                SqlDataReader dataReader = ExportHelper.GetDataReader(connection, sqlStatement.ToString());

                strWriter = new StreamWriter(fileName.ToString(), false);
                ExportHelper.Export2Csv(ref dataReader, ref strWriter, fieldDelimiter.ToString(), rowDelimiter.ToString(), columnHeader.Value);
                strWriter.Close();
                strWriter.Dispose();
                dataReader.Close();
                connection.Close();
            }
        }

    }
};
